<?php
class data {
    public function init($params=array()){
		$this->db=$this->yum->library('mysql');
		$this->prefix=$this->yum->vars('app','prefix');
	}
	public function select($table,$params){
		return $this->db->select($this->prefix.'data_'.$table.' AS A',$params);
	}
	public function insert($table,$params){
		return $this->db->insert($this->prefix.'data_'.$table,$params);
	}
	public function update($table,$params){
		return $this->db->update($this->prefix.'data_'.$table,$params);
	}
	public function replace($table,$params){
		return $this->db->replace($this->prefix.'data_'.$table,$params);
	}
	public function transform($params){
		$table.='(SELECT A.order,A.name,SUM(A.money) AS money,A.number,A.rate,SUM(A.commision) AS commision,A.time,A.state,A.date,A.ip,B.mail AS member,A.locked FROM ';
    	$table.=$this->prefix.'data_transform AS A';
    	$table.=' LEFT JOIN '.$this->prefix.'member AS B ON A.member=B.id GROUP BY `order`) AS C';
    	return $this->db->select($table,$params);
	}
	function table($action,$mix,$max,$date){
		if($action=='pageview'){
			$insert='INSERT '.$this->prefix.'data_table(`date`,`site`,`billboard`,`usage`,`modality`,`format`,`advertisers`,`province`,`city`,`browser`,`version`,`lang`,`system`,`screen`,`impression`)';
			$insert.=' SELECT '.$date.',site,billboard,`usage`,modality,format,advertisers,province,city,browser,version,lang,system,screen,COUNT(*) FROM '.$this->prefix.'data_pageview WHERE `date`>='.$mix.' AND `date`<'.$max.' group by site,billboard,`usage`,modality,format,advertisers,province,city,browser,version,lang,system,screen';
			$affected=$this->db->query($insert);
			if($affected!==false){
				return true;
			}else{
				return false;
			}
		}else if($action=='track'){
			$insert='INSERT '.$this->prefix.'data_table(`date`,`site`,`billboard`,`usage`,`modality`,`format`,`advertisers`,`province`,`city`,`browser`,`version`,`lang`,`system`,`screen`,`click`)';
			$insert.=' SELECT '.$date.',site,billboard,`usage`,modality,format,advertisers,province,city,browser,version,lang,system,screen,COUNT(*) AS click FROM '.$this->prefix.'data_pageview AS A,(SELECT pageview,visitor,track FROM '.$this->prefix.'data_track WHERE action=\'click\' AND `date`>='.$mix.' AND `date`<'.$max.' group by visitor,track) AS B WHERE A.date=B.pageview AND A.visitor=B.visitor AND A.track=B.track group by site,billboard,`usage`,modality,format,advertisers,province,city,browser,version,lang,system,screen';
			$affected=$this->db->query($insert);
			if($affected!==false){
				$insert='INSERT '.$this->prefix.'data_table(`date`,`site`,`billboard`,`usage`,`modality`,`format`,`advertisers`,`province`,`city`,`browser`,`version`,`lang`,`system`,`screen`,`reach`)';
				$insert.=' SELECT '.$date.',site,billboard,`usage`,modality,format,advertisers,province,city,browser,version,lang,system,screen,COUNT(*) AS reach FROM '.$this->prefix.'data_pageview AS A,(SELECT pageview,visitor,track FROM '.$this->prefix.'data_track WHERE action=\'reach\' AND `date`>='.$mix.' AND `date`<'.$max.' group by visitor,track) AS B WHERE A.date=B.pageview AND A.visitor=B.visitor AND A.track=B.track group by site,billboard,`usage`,modality,format,advertisers,province,city,browser,version,lang,system,screen';
				$affected=$this->db->query($insert);
				if($affected!==false){
					return true;
				}else{
					return false;
				}
			}else{
				return false;
			}
		}elseif($action=='transform'){
			$TEMP='SELECT A.visitor,A.track,A.date,A.site,A.billboard,A.`usage`,A.modality,A.format,A.advertisers,A.province,A.city,A.browser,A.version,A.lang,A.system,A.screen,B.fee FROM '.$this->prefix.'data_pageview AS A,(SELECT visitor,track,pageview AS date,SUM(commision) AS fee FROM `'.$this->prefix.'data_transform` WHERE `date`>='.$mix.' AND `date`<'.$max.' AND pageview!=0 GROUP BY `order`) AS B WHERE A.`date`=B.date AND A.visitor=B.visitor AND A.track=B.track;';
			$TEMP=$this->db->query($TEMP);
			foreach($TEMP as $value){
				$value['date']=(((int)(($value['date']+28800)/86400))*86400)-28800;
				$C=$this->select('transform',array('where'=>array('date <'=>$mix,'pageview >='=>$value['date'],'pageview <'=>$value['date']+86400,'visitor ='=>$value['visitor'],'track ='=>$value['track'])));
				$U=array('set'=>array('fee =fee+'=>$value['fee'],'shop =shop+'=>1),'where'=>array('`date`='=>$value['date'],'site='=>$value['site'],'billboard='=>$value['billboard'],'`usage`='=>$value['usage'],'modality='=>$value['modality'],'format='=>$value['format'],'advertisers='=>$value['advertisers'],'province='=>$value['province'],'city='=>$value['city'],'browser='=>$value['browser'],'version='=>$value['version'],'lang='=>$value['lang'],'system='=>$value['system'],'screen='=>$value['screen']));
				if(empty($C)){$U['set']['transform =transform+']=1;}
				$this->update('table',$U);
			}
			return true;
		}elseif($action=='unite'){
			$TEMP='CREATE TEMPORARY TABLE IF NOT EXISTS BASIC(
			SELECT `date`,site,billboard,`usage`,modality,format,advertisers,province,city,browser,version,lang,system,screen,SUM(`impression`),SUM(`click`),SUM(`reach`),SUM(`transform`),SUM(`shop`),SUM(`fee`) FROM `'.$this->prefix.'data_table` WHERE `date`='.$date.' GROUP BY `date`,site,billboard,`usage`,modality,format,advertisers,province,city,browser,version,lang,system,screen
			);';
			$this->db->query($TEMP);
			$affected=$this->db->delete($this->prefix.'data_table',array('where'=>array('`date` ='=>$date)));
			if($affected===false){
				return false;
			}
			$insert='INSERT INTO `'.$this->prefix.'data_table`(`date`,site,billboard,`usage`,modality,format,advertisers,province,city,browser,version,lang,system,screen,`impression`,`click`,`reach`,`transform`,`shop`,`fee`)';
			$insert.=' SELECT * FROM BASIC';
			$affected=$this->db->query($insert);
			if($affected!==false){
				$this->db->query('DROP TEMPORARY TABLE IF EXISTS BASIC');
				return true;
			}else{
				return false;
			}
		}
	}
	function chart($mix,$max,$date){
		$data['impression']=reset($this->select('pageview',array('select'=>'COUNT(*) AS impression','where'=>array('date >='=>$mix,'date <'=>$max))));
		$data['impression']=intval($data['impression']['impression']);
		$track=$this->select('track',array('select'=>'action,COUNT(*) AS number','where'=>array('date >='=>$mix,'date <'=>$max,'action IN'=>array('click','reach')),'group'=>array('action')));
		$data[$track[0]['action']]=$track[0]['number'];$data[$track[1]['action']]=$track[1]['number'];$data['click']=intval($data['click']);$data['reach']=intval($data['reach']);
		//新增加的展示,点击,到达数据
		$current=array('impression =impression+'=>$data['impression'],'click =click+'=>$data['click'],'reach =reach+'=>$data['reach']);
		//处理当前时间天数据
		$ivisitor=reset($this->db->query('SELECT COUNT(*) AS ivisitor FROM (SELECT visitor FROM '.$this->prefix.'data_pageview WHERE `date`>='.$date.' AND `date`<'.($date+86400).' GROUP BY visitor) AS A'));
		$track=$this->db->query('SELECT action,COUNT(*) AS number FROM (SELECT action,visitor FROM '.$this->prefix.'data_track WHERE `date`>='.$date.' AND `date`<'.($date+86400).' GROUP BY action,visitor) AS A GROUP BY action');
		$temp[$track[0]['action']]=$track[0]['number'];$temp[$track[1]['action']]=$track[1]['number'];
		$current['ivisitor =']=intval($ivisitor['ivisitor']);
		$current['cvisitor =']=intval($temp['click']);$current['rvisitor =']=intval($temp['reach']);
		$C=$this->select('chart',array('where'=>array('date='=>$date)));
		if(empty($C)){
			$this->insert('chart',array('date'=>$date,'impression'=>$data['impression'],'click'=>$data['click'],'reach'=>$data['reach'],'ivisitor'=>$current['ivisitor ='],'cvisitor'=>$current['cvisitor ='],'rvisitor'=>$current['rvisitor =']));
		}else{
			$this->update('chart',array('where'=>array('date='=>$date),'set'=>$current));
		}
		//处理当前时间所在周数据
		$week=$date-intval(date('N',$date))*86400+86400;$temp=array();
		$ivisitor=reset($this->db->query('SELECT COUNT(*) AS ivisitor FROM (SELECT visitor FROM '.$this->prefix.'data_pageview WHERE `date`>='.$week.' AND `date`<'.($week+7*86400).' GROUP BY visitor) AS A'));
		$track=$this->db->query('SELECT action,COUNT(*) AS number FROM (SELECT action,visitor FROM '.$this->prefix.'data_track WHERE `date`>='.$week.' AND `date`<'.($week+7*86400).' GROUP BY action,visitor) AS A GROUP BY action');
		$temp[$track[0]['action']]=$track[0]['number'];$temp[$track[1]['action']]=$track[1]['number'];
		$current['ivisitor =']=intval($ivisitor['ivisitor']);
		$current['cvisitor =']=intval($temp['click']);$current['rvisitor =']=intval($temp['reach']);
		$week=intval(date('Ymd',$week));
		$C=$this->select('chart',array('where'=>array('date='=>$week)));
		if(empty($C)){
			$this->insert('chart',array('date'=>$week,'impression'=>$data['impression'],'click'=>$data['click'],'reach'=>$data['reach'],'ivisitor'=>$current['ivisitor ='],'cvisitor'=>$current['cvisitor ='],'rvisitor'=>$current['rvisitor =']));
		}else{
			$this->update('chart',array('where'=>array('date='=>$week),'set'=>$current));
		}
		//处理当前时间所在月数据
		$month=$date-intval(date('j',$date))*86400+86400;$temp=array();
		$ivisitor=reset($this->db->query('SELECT COUNT(*) AS ivisitor FROM (SELECT visitor FROM '.$this->prefix.'data_pageview WHERE `date`>='.$month.' AND `date`<'.($month+intval(date('t',$month))*86400).' GROUP BY visitor) AS A'));
		$track=$this->db->query('SELECT action,COUNT(*) AS number FROM (SELECT action,visitor FROM '.$this->prefix.'data_track WHERE `date`>='.$month.' AND `date`<'.($month+intval(date('t',$month))*86400).' GROUP BY action,visitor) AS A GROUP BY action');
		$temp[$track[0]['action']]=$track[0]['number'];$temp[$track[1]['action']]=$track[1]['number'];
		$current['ivisitor =']=intval($ivisitor['ivisitor']);
		$current['cvisitor =']=intval($temp['click']);$current['rvisitor =']=intval($temp['reach']);
		$month=intval(date('Ym',$month));
		$C=$this->select('chart',array('where'=>array('date='=>$month)));
		if(empty($C)){
			$this->insert('chart',array('date'=>$month,'impression'=>$data['impression'],'click'=>$data['click'],'reach'=>$data['reach'],'ivisitor'=>$current['ivisitor ='],'cvisitor'=>$current['cvisitor ='],'rvisitor'=>$current['rvisitor =']));
		}else{
			$this->update('chart',array('where'=>array('date='=>$month),'set'=>$current));
		}
		//处理转化数据
		$temp=$this->db->query('SELECT pageview,visitor,track,COUNT(*) AS shop,SUM(fee) AS fee FROM (SELECT pageview,visitor,track,SUM(commision) AS fee FROM `'.$this->prefix.'data_transform` WHERE date>='.$mix.' AND date<'.$max.' GROUP BY `order`) AS A GROUP BY visitor,track');
		$data['transform']=array();
		foreach($temp as $value){
			$value['pageview']=(((int)(($value['pageview']+28800)/86400))*86400)-28800;
			$data['transform'][$value['pageview']]['shop']+=$value['shop'];
			$data['transform'][$value['pageview']]['fee']+=$value['fee'];
		}
		$chart_week=array();$chart_month=array();
		foreach($data['transform'] as $key=>$value){//天转化数据
			$set=array();
			$set['shop=shop+']=$value['shop'];$set['fee=fee+']=$value['fee'];
			$transform=reset($this->db->query('SELECT COUNT(*) AS transform FROM (SELECT * FROM '.$this->prefix.'data_transform WHERE pageview>='.$key.' AND pageview<'.($key+86400).' GROUP BY visitor,track) AS A'));
			$set['transform =']=intval($transform['transform']);
			$tvisitor=reset($this->db->query('SELECT COUNT(*) AS tvisitor FROM (SELECT * FROM '.$this->prefix.'data_transform WHERE pageview>='.$key.' AND pageview<'.($key+86400).' GROUP BY visitor) AS A'));
			$set['tvisitor =']=intval($tvisitor['tvisitor']);
			$this->update('chart',array('where'=>array('date='=>$key),'set'=>$set));

			$week=$key-intval(date('N',$key))*86400+86400;//每周第一天
			$chart_week[$week]['shop']+=$value['shop'];$chart_week[$week]['fee']+=$value['fee'];
			$month=$key-intval(date('j',$key))*86400+86400;//每月第一天
			$chart_month[$month]['shop']+=$value['shop'];$chart_month[$month]['fee']+=$value['fee'];
		}
		foreach($chart_week as $key=>$value){//周转化数据
			$set=array();
			$set['shop=shop+']=$value['shop'];$set['fee=fee+']=$value['fee'];
			$transform=reset($this->db->query('SELECT COUNT(*) AS transform FROM (SELECT * FROM '.$this->prefix.'data_transform WHERE pageview>='.$key.' AND pageview<'.($key+7*86400).' GROUP BY visitor,track) AS A'));
			$set['transform =']=intval($transform['transform']);
			$tvisitor=reset($this->db->query('SELECT COUNT(*) AS tvisitor FROM (SELECT * FROM '.$this->prefix.'data_transform WHERE pageview>='.$key.' AND pageview<'.($key+7*86400).' GROUP BY visitor) AS A'));
			$set['tvisitor =']=intval($tvisitor['tvisitor']);
			$this->update('chart',array('where'=>array('date='=>intval(date('Ymd',$key))),'set'=>$set));
		}
		foreach($chart_month as $key=>$value){//月转化数据
			$set=array();
			$set['shop=shop+']=$value['shop'];$set['fee=fee+']=$value['fee'];
			$transform=reset($this->db->query('SELECT COUNT(*) AS transform FROM (SELECT * FROM '.$this->prefix.'data_transform WHERE pageview>='.$key.' AND pageview<'.($key+intval(date('t',$key))*86400).' GROUP BY visitor,track) AS A'));
			$set['transform =']=intval($transform['transform']);
			$tvisitor=reset($this->db->query('SELECT COUNT(*) AS tvisitor FROM (SELECT * FROM '.$this->prefix.'data_transform WHERE pageview>='.$key.' AND pageview<'.($key+intval(date('t',$key))*86400).' GROUP BY visitor) AS A'));
			$set['tvisitor =']=intval($tvisitor['tvisitor']);
			$this->update('chart',array('where'=>array('date='=>intval(date('Ym',$key))),'set'=>$set));
		}
		return true;
	}
}
?>